﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_Business;

namespace Business.FlowOperation
{
    /// <summary>
    /// 挂起信息操作
    /// </summary>
    public class SuspendQueryService
    {
        /// <summary>
        /// 根据userid和期限查找顺序号集合
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="dtTemp"></param>
        public void GetIidByOverTimeAndUserId(string userId, out DataTable dtTemp) 
        {
            string strSql = "select 顺序号 from sv_work_list_for_worklist_node where 结点过期<0 and userid='" + userId + "' ";

            SysParams.OAConnection().RunSql(strSql, out dtTemp);
        }
        /// <summary>
        /// 得到挂起案例集合
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetHangCaseList(string strXmmc,string strDis,string strserialapplyer,string strapplyer,string d_s,string d_e,string strIID,string userId)
        {
            string sql = string.Empty;
            DataTable rs1;
            sql = @"select a.serial,a.handleid,a.suspend_type,a.userid,apply_time,a.memo,a.id,
                                          (select start_date from st_suspend where 1 = 2) start_date
                                          from xt_suspend_apply a where 1 = 1 and a.userid = '{0}' {1} {2} {3} and a.checkuserid is null
                                          union all 
                                          select a.serial,a.handleid,a.suspend_type,a.userid,apply_time,a.memo,a.id,b.start_date
                                          from xt_suspend_apply a, st_suspend b where a.id = b.xt_suspend_id and a.userid = '{0}' {1} {2} {3} 
                                          and b.end_userid is null";
            string startData = "";
            string endData = "";
            string strWhereIID = "";
            if ((!String.IsNullOrEmpty(d_s)) && (!String.IsNullOrEmpty(d_e)))
            {
                startData = string.Format(" and a.apply_time>=to_date('{0}','YYYY-MM-DD HH24:MI:SS')", d_s);
                endData = string.Format(" and a.apply_time<=to_date('{0}','YYYY-MM-DD HH24:MI:SS')", d_e);

            }
            if (!string.IsNullOrEmpty(strIID))
            {
                strWhereIID = string.Format(" and a.serial like '%{0}%'", strIID);
            }
            sql = string.Format(sql, userId, startData, endData, strWhereIID);
            SysParams.OAConnection().RunSql(sql, out rs1);
            return rs1;
        }

        /// <summary>
        /// 根据iid查找备注信息
        /// </summary>
        /// <param name="iid"></param>
        /// <returns></returns>
        public string ShowRemarkByIId(System.String iid)
        {
            System.String strSql = "select remark from st_work_item where wiid=" + iid + " and isback = '1'";
            return SysParams.OAConnection().GetValue(strSql);
        }

        /// <summary>
        /// 获得当前用户的姓名
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public string GetCurrentUserName(string userId)
        {
            string sql = "select user_name from st_user where userid='" + userId + "' ";
            return SysParams.OAConnection().GetValue(sql);
        }

        /// <summary>
        /// 获得当前岗位的名称
        /// </summary>
        /// <param name="wiid"></param>
        /// <returns></returns>
        public string GetCurrentGwName(string wiid)
        {
            string sql = "select step from st_work_item where wiid='" + wiid + "' ";
            return SysParams.OAConnection().GetValue(sql);
        }
        /// <summary>
        /// 添加业务删除标识条件 and i.isdelete <>1
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetWnameByCondi(string id)
        {
            string strSqlWname = string.Format("select wname from st_workflow where wid=(select wid from  st_instance where iid='{0}' and isdelete <>1)", id);
            return SysParams.OAConnection().GetValue(strSqlWname);
        }
        /// <summary>
        ///根据当前登录用户ID查询出serial_id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetWidByCondi(string id)
        {
            string strSerialid = string.Format("select f.wid from  st_instance f where f.iid='" + id + "' and isdelete <>1");
            return SysParams.OAConnection().GetValue(strSerialid);
        }
        /// <summary>
        /// 根据当前登录用户ID查询出FLOW_NO
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="wname"></param>
        /// <returns></returns>
        public string GetFlow_NoByUserIdAndWname(string userId, string wname)
        {
            string strUserNo = string.Format("select distinct a.FLOW_NO from XT_SUSPEND_FLOW a,XT_SUSPENDFLOW_USERID b where a.ID=b.FLOW_ID and b.USERID='{0}' and b.serial_id in( select wid from st_workflow where wname='{1}')", userId, wname);
            return SysParams.OAConnection().GetValue(strUserNo);
        }
        /// <summary>
        /// 根据id和s_gwId取得当前的记录
        /// </summary>
        /// <param name="id"></param>
        /// <param name="s_gwId"></param>
        /// <returns></returns>
        public DataTable GetCurrentListByIdAndHandleId(string id, string s_gwId)
        {
            DataTable rs;
            string sql = "select suspend_type,memo,id from xt_suspend_apply where serial='" + id + "' and handleid='" + s_gwId + "' and ischeckup is null and isdelete is null";
            SysParams.OAConnection().RunSql(sql, out rs);
            return rs;
        }

        public void OperatSuspend_Add(System.String id, string Wname, System.String s_id, System.String s_type, int nextUserStepNo,string sql)
        {
            SysParams.OAConnection().RunSql(sql);
            if (s_type.Equals("gqsq"))
            {
                //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                string messageText = "您在政务平台中有新的挂起申请业务待审批，请尽快处理！业务编号：" + id;
                string strSql = string.Format(@"select login_name,user_name,mobile from st_user where userid in(select b.userid from XT_SUSPEND_FLOW a, XT_SUSPENDFLOW_USERID b
                            where a.flow_no = '{0}' and a.id = b.flow_id and b.serial_id in(select a.wid
                            from st_workflow a where a.wname='{1}'))", nextUserStepNo, Wname);

                DataTable dtMessage;
                SysParams.OAConnection().RunSql(strSql, out dtMessage);
                foreach (DataRow rowMessage in dtMessage.Rows)
                {
                    //添加用户ID addby zhongjian 20091029
                    strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                             messageText, rowMessage["mobile"].ToString(), rowMessage["user_name"].ToString(), rowMessage["login_name"].ToString());
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
            else if (s_type.Equals("qxsq"))
            {
                //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                string messageText = "业务编号为：" + id + " 的挂起申请业务已经取消申请！";
                string nexeNo = string.Format("(select nextno from xt_suspend_apply where id='{0}')", s_id);
                string strSql = string.Format(@"select login_name,user_name,mobile from st_user where userid in(select b.userid from XT_SUSPEND_FLOW a, XT_SUSPENDFLOW_USERID b
                            where a.flow_no = {0} and a.id = b.flow_id and b.serial_id in(select a.wid
                            from st_workflow a where a.wname='{1}'))", nexeNo, Wname);

                DataTable dtMessage;
                SysParams.OAConnection().RunSql(strSql, out dtMessage);
                foreach (DataRow rowMessage in dtMessage.Rows)
                {
                    //添加用户ID addby zhongjian 20091029
                    strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                             messageText, rowMessage["mobile"].ToString(), rowMessage["user_name"].ToString(), rowMessage["login_name"].ToString());
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
            else if (s_type.Equals("sqjg"))//申请解挂
            {
                //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                string messageText = "业务编号为：" + id + " 的挂起业务现申请解挂，请尽快处理！";
                string nexeNo = string.Format("(select nextno from xt_suspend_apply where id='{0}')", s_id);
                string strSql = string.Format(@"select login_name,user_name,mobile from st_user where userid in(select b.userid from XT_SUSPEND_FLOW a, XT_SUSPENDFLOW_USERID b
                            where a.flow_no = {0} and a.id = b.flow_id and b.serial_id in(select a.wid
                            from st_workflow a where a.wname='{1}'))", nexeNo, Wname);

                DataTable dtMessage;
                SysParams.OAConnection().RunSql(strSql, out dtMessage);
                foreach (DataRow rowMessage in dtMessage.Rows)
                {

                    strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                             messageText, rowMessage["mobile"].ToString(), rowMessage["user_name"].ToString(), rowMessage["login_name"].ToString());
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
            else if (s_type.Equals("jcgq"))
            {
                string messageText = "业务编号为：" + id + " 的案件现已成功解挂，请查看！";
                string strSql = string.Format(@"select t.login_name,t.user_name,t.mobile from st_user t where t.userid = (select a.userid from xt_suspend_apply a where a.id='{0}')", s_id);
                DataTable dtTemp = new DataTable();
                SysParams.OAConnection().RunSql(strSql, out dtTemp);
                if (dtTemp.Rows.Count > 0)
                {
                    strSql = string.Format(@"insert into xt_MESSAGEBOX(MESSAGETEXT,PHONENO,USERNAME,USERID) values('{0}','{1}','{2}','{3}')",
                           messageText, dtTemp.Rows[0]["mobile"].ToString(), dtTemp.Rows[0]["user_name"].ToString(), dtTemp.Rows[0]["login_name"].ToString());
                    SysParams.OAConnection().RunSql(strSql);
                }
            }
        }

        public string HandleOperate(string userId,string wName, System.String s_userid, ref System.String id, ref System.String s_gwId, ref System.String s_memo, string Wname, ref System.String s_suspentype, System.String s_id, ref System.String s_gwname, System.String s_type, ref System.String jsHtml, ref int nextUserStepNo)
        {
            string sql = "";
            DataTable rs=null;
            if (s_type.Equals("gqsq"))//挂起申请
            {
                //根据当前登录用户ID查询出serial_id
                string s_serialid = GetWidByCondi(id);

                //根据当前登录用户ID查询出FLOW_NO
                string curUserStepNo = GetFlow_NoByUserIdAndWname(userId, wName);

                //根据业务ID查询出大于当前登录用户Flow_No的最小Flow_No---update by lj
                //由于版本id的问题，特将按照版本id来查找，改为按照流程名称来查找
                string MinFlowNo = string.Format(@"select min(a.flow_no) from xt_suspend_flow a where a.id in
            (select t.flow_id from xt_suspendflow_userid t where t.serial_id in( select wid from st_workflow where wname= '{0}')) and a.flow_no>'{1}'",
                    Wname, curUserStepNo);
                string s_MinFlowNo = SysParams.OAConnection().GetValue(MinFlowNo);

                //if 当期用户没有配置在Xt_suspendflow_userid中
                if (!string.IsNullOrEmpty(s_MinFlowNo))
                {
                    nextUserStepNo = Convert.ToInt32(s_MinFlowNo);

                    if (nextUserStepNo < 1)
                    {
                        jsHtml = "<script>alert('请联系管理员配置挂起申请的审批人');history.back(-1);</script>";
                    }
                    else
                    {
                        //挂起申请
                        sql = "insert into xt_suspend_apply(serial,handleid,suspend_type,userid,apply_time,memo,nextno)";
                        sql += ("values('" + id + "','" + s_gwId + "','" + s_suspentype + "','" + s_userid + "',sysdate,'" + s_memo + "','" + nextUserStepNo + "')");
                    }
                }
                else
                {
                    jsHtml = "<script>alert('请联系管理员配置挂起申请的审批人');history.back(-1);</script>";
                }
            }
            else if (s_type.Equals("qxsq") || s_type.Equals("06"))//取消挂起申请
            {
                sql = " update xt_suspend_apply set isdelete='1' ,checkuserid='" + s_userid + "',delete_time=sysdate where id='" + s_id + "'";
            }
            else if (s_type.Equals("sqjg"))//申请解挂
            {
                //不做任何操作 只需要发送短信即可		
                sql = "select * from st_suspend where 1=2";
            }
            else if (s_type.Equals("pzgq"))//批准挂起
            {
                sql = "update xt_suspend_apply set checkuserid='" + s_userid + "',check_time=sysdate,ischeckup='1'";
                sql += (" where id='" + s_id + "'");

                SysParams.OAConnection().RunSql(sql);
                sql = "select serial,handleid,suspend_type,memo from xt_suspend_apply where id='" + s_id + "'";

                SysParams.OAConnection().RunSql(sql, out rs);
                //获得预申请信息，插入挂起表
                if (rs.Rows.Count > 0)
                {
                    id = rs.Rows[0][0].ToString();
                    s_gwId = rs.Rows[0][1].ToString();
                    s_suspentype = rs.Rows[0][2].ToString();
                    s_memo = rs.Rows[0][3].ToString();
                }
                if (s_gwId != null)
                {
                    //获得当前岗位的名称
                    sql = "select step from st_work_item where wiid='" + s_gwId + "' ";
                    s_gwname = SysParams.OAConnection().GetValue(sql);
                }
                sql = "insert into st_suspend(iid,start_date,userid,description,stpname,xt_suspend_id,suspend_type)";
                sql += ("values('" + id + "',sysdate ,'" + s_userid + "','" + s_memo + "','" + s_gwname + "','" + s_id + "','" + s_suspentype + "')");
            }
            else if (s_type.Equals("jcgq"))//审批人取消挂起
            {
                sql = " update xt_suspend_apply set checkuserid='" + s_userid + "',delete_time=sysdate ,ischeckup='2' where id='" + s_id + "'";
                SysParams.OAConnection().RunSql(sql);


                //sql = string.Format("delete from st_suspend where iid='{0}'",id);

                //修改人:  LiuY
                //修改时间:2008-10-07
                //修改原因:防止取消挂起后删除st_suspend表中数据，使得案件办理时间增加
                sql = string.Format("update st_suspend set end_date=sysdate ,end_userid='{0}' where xt_suspend_id='{1}' ", s_userid, s_id);

            }
            return sql;
        }

    }
}
